import pandas as pd
from  pyecharts import Bar,Pie
import sqlite3
import redis
import json

if __name__ == '__main__':

    db_path = '../db.sqlite3'

else:

    db_path = 'db.sqlite3'

conn = sqlite3.connect(db_path,check_same_thread=False)

class Move:

    def __init__(self):

        #conn = sqlite3.connect(db_path)

        self.df = pd.read_sql('SELECT * FROM DMSC_csv',conn)

        self.r = redis.Redis(host='172.16.44.57', port=6379, password='1', db='0')
        #self.r = redis.Redis(host='192.168.43.151', port=6379, password='1', db='0')

#1、每部电影有多少个用户参与评论，并做出bar 图（10）
    def  make_most_user_comment(self):

        df2 = self.df

        df1 = df2.groupby('Movie_Name_CN').count()['index']
        re = self.r
        dic = df1.to_dict()
        dic_js = json.dumps(dic)
        re.set("Movie_Name_CN",dic_js)
        return df1
#2、每部电影的点赞数量总计，并做出bar 图（10）
    def Total_thumb_up_pe_film_sum(self):

        df2 = self.df

        df1 = df2.groupby('Movie_Name_CN').sum()['index']

        re = self.r

        dic = df1.to_dict()

        dic_js = json.dumps(dic)

        re.set("Movie_Name_CN", dic_js)


        return df1
# 3、收到点赞最多的用户（5）
    def every_move_max_like(self):
        df2 = self.df

        df3 = df2.groupby('Username').sum().sort_values('Like', ascending=False)['Like']

        return df3
# 4、每个电影的平均评分,并做出bar图（5）
    def every_move_star_average_score(self):

        df2 = self.df

        df3 = df2.groupby('Movie_Name_CN').mean()['Star']

        return df3
# 5、为了真实反应电影的平均分数，计算电影平均分时将’Like‘少于100的数据不计算在内，重新计算平均分数，并做出bar图（10）；
    def every_move_star_greater_average_score(self):

        df2 = self.df

        df1 = df2[df2.Like>=100]

        df3 = df1.groupby('Movie_Name_CN').mean()['Star']

        return df3
# 6、将发布评论最多的前20个用户及对应的发布量，做出bar图，（10）
    def The_top_20_users_with_the_most_comments(self):

        df1 = self.df

        df2 = df1.groupby('Username')['Comment'].count().sort_values(ascending=False).head(20)

        return df2
# 7、将每部电影'Like'值最高的前5个评论展示出来（10）
    def The_top_5_most_like_reviews_per_movie(self):

        df1 =self.df

        df2 = df1.groupby('Movie_Name_CN').sum()

        df3 = df2.index

        df2 = df1.loc[:, ['Movie_Name_CN', 'Username', 'Comment', 'Like']]

        y = 0

        for i in df3:

            x = df2[df2.Movie_Name_CN == i].sort_values('Like', ascending=False).head(5)

            y += 1

            return info
# 8、将每部电影评分（star值）为2及以下的评论中’Like'值最高的前三个评论显示出来；（10）
    def every_move_star_small_two_like_stree(self):

        df1 = self.df

        df2 = df1[df1.Star <= 2]
        df3 = df2.groupby('Movie_Name_CN').sum()
        df4 = df3.index
        df5 = df2.loc[:, ['Movie_Name_CN', 'Username', 'Comment', 'Like']]
        y = 0
        for i in df4:
            x = df5[df5.Movie_Name_CN == i].sort_values('Like', ascending=False).head(3)
            y += 1

            info = x.to_dict()

            return info
# 9、将’复仇者联盟2‘电影的每天评论的数量做成Line图（10）
    def The_number_of_daily_reviews_of_the_film(self):

        df1 = self.df

        df2 = df1[df1.Movie_Name_CN == '复仇者联盟2'].groupby('Date').Comment.count()

        return df2
# 10、请将职业“影黑人士”找出来，（10）
    def  Dark_people_move(self):

        df1 = self.df

        df2 = df1[df1.Star <= 2]['Username']

        return df2
#
    def get_top_five_user_content(self):
        df1 = self.df
        df2 =df1.sort_values(by=['Movie_Name_CN', 'Like'], ascending=False).groupby('Movie_Name_CN').head(5).drop(
            ['ID', 'Movie_Name_EN', 'Crawl_Date', 'Number', 'Username', 'Date', 'Star'], axis=1)

        #info = df2.to_dict()

        # cont = {
        #     "move_name":info,
        #        "Comment":info,
        # }
        #
        # re = self.r
        #
        # re.set("Movie_Name_CN", info)
        # p = re.get("Movie_Name_CN")



        return df2


class Me_Hash:
    def my_hash(self):
        r = redis.Redis(host='172.16.44.57', port=6379, password='1', db='0')
        dic = {"a1": "aa", "b1": "bb"}
        r.hmset("dic_name", dic)
        print(r.hget("dic_name", "b1"))



if __name__ =="__main__":
    x = Move()
    info = x.get_top_five_user_content()








